package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据实现层
 * 1. 封装sql语句
 * 2.得到返数据--通过什么连接到数据库   JDBC tomcat
 * 3.封装返回值
 */
public class UserImpl implements UserDao {
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where " +
                "username = '" + userModel.getUsername() + "' " +
                "and password = '" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.query(sql);
        UserModel model = null;
        try {
//            表示如果resultSet有下一条数据
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public UserModel findById(Integer id) {
        String sql = "select * from user where id=" + id;
//        ResultSet resultSet = JDBC.query(sql);
//        UserModel model = null;
//        try {
////            表示如果resultSet有下一条数据
//            while (resultSet.next()) {
//                model = new UserModel();
////                将数据库对应的列里面的数据赋值给model对应的属性
//                model.setId(resultSet.getInt("id"));
//                model.setUsername(resultSet.getString("username"));
//                model.setPhone(resultSet.getString("phone"));
//                model.setCreateTime(resultSet.getString("create_time"));
//                model.setUpdateTime(resultSet.getDate("update_time"));
//                model.setEnable(resultSet.getInt("enable"));
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
        int size = getData(sql).size();
//        if (size > 0) {
//            return getData(sql).get(0);
//        }
        return size > 0 ? getData(sql).get(0) : null;
    }

    @Override
    public int save(UserModel userModel) {
        String sql = "insert into user(username,password,phone,sex,create_time,update_time,hobby) values (" +
                "'" + userModel.getUsername() + "'," +
                "'" + userModel.getPassword() + "'," +
                "'" + userModel.getPhone() + "'," +
                "'" + userModel.getSex() + "'," +
                "now()," +
                "now(),'" + userModel.getHobby() + "')";
        return JDBC.update(sql);
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "update user set " +
                "username='" + userModel.getUsername() + "' ," +
                "password='" + userModel.getPassword() + "'," +
                "phone='" + userModel.getPhone() + "'," +
                "hobby='" + userModel.getHobby() + "'," +
                "update_time=now()  where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int delete(Integer id) {
        String sql = "DELETE FROM user WHERE id=" + id;
        return JDBC.update(sql);
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = " select id,username,sex,phone,enable,date_format(birthday,'%y/%m/%d') birthday,hobby,email,create_time,update_time " +
                "from user where true";
        if (!userModel.getUsername().equals("")) {
            sql += "  and  username like '%" + userModel.getUsername() + "%' ";
        }
        if (!userModel.getSex().equals("")) {
            sql += " and sex = '" + userModel.getSex() + "' ";
        }
        if (userModel.getEnable() != -1) {
            sql += "  and  enable =  " + userModel.getEnable() + " ";
        }
        if (userModel.getStart().equals("") && !userModel.getEnd().equals("")) {
            sql += "  and birthday <='" + userModel.getEnd() + "'";
        }
        if (!userModel.getStart().equals("") && userModel.getEnd().equals("")) {
            sql += " and  birthday >='" + userModel.getStart() + "'";
        }
        if (!userModel.getStart().equals("") && !userModel.getEnd().equals("")) {
            sql += "  and birthday between '" + userModel.getStart() + "' and '" + userModel.getEnd() + "'";
        }

        if (!userModel.getHobby().equals("")) {
            String[] hobbys = userModel.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql += " find_in_set(" + hobbys[i] + ",hobby)";
                } else {
                    sql += " or find_in_set(" + hobbys[i] + ",hobby)";
                }

            }
            sql += " )";
//            int i = hobbys.length;//旅游，看书，游戏
//            while (i >= 0) {
//                sql += " and find_in_set(" + hobbys[i] + ",hobby)";
//                i--;
//            }
//            取得数组的值，删除第一个，得到新数组
//            while (hobbys.length>0){
//                sql += " and find_in_set(" + hobbys[0] + ",hobby)";
//
//            }
        }
//        sql += "  username like '%" + userModel.getUsername() + "%' ";
//        sql += "  username like '%" + userModel.getUsername() + "%' ";
//        int start=(pageBean.getPage()-1)*pageBean.getPageSize();
        sql += " order by id desc ";
        sql += " limit " + (userModel.getPageBean().getPage() - 1) * userModel.getPageBean().getPageSize() + "," + userModel.getPageBean().getPageSize() + ";";
        return getData(sql);

    }

    @Override
    public int updateEnable(UserModel userModel) {
        String sql = "update user set enable=abs(enable-1) where id=" + userModel.getId();

        return JDBC.update(sql);
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) count from user where true";
        if (!userModel.getUsername().equals("")) {
            sql += "  and  username like '%" + userModel.getUsername() + "%' ";
        }
        if (!userModel.getSex().equals("")) {
            sql += " and sex = '" + userModel.getSex() + "' ";
        }
        if (userModel.getEnable() != -1) {
            sql += "  and  enable =  " + userModel.getEnable() + " ";
        }
        if (userModel.getStart().equals("") && !userModel.getEnd().equals("")) {
            sql += "  and birthday <='" + userModel.getEnd() + "'";
        }
        if (!userModel.getStart().equals("") && userModel.getEnd().equals("")) {
            sql += " and  birthday >='" + userModel.getStart() + "'";
        }
        if (!userModel.getStart().equals("") && !userModel.getEnd().equals("")) {
            sql += "  and birthday between '" + userModel.getStart() + "' and '" + userModel.getEnd() + "'";
        }

//        sql += "  username like '%" + userModel.getUsername() + "%' ";
//        sql += "  username like '%" + userModel.getUsername() + "%' ";
//        int start=(pageBean.getPage()-1)*pageBean.getPageSize();
        sql += " order by id desc ";
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public UserModel findByUsername(String username) {
        return null;
    }


    private List<UserModel> getData(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setSex(resultSet.getString("sex"));
                model.setEmail(resultSet.getString("email"));
                model.setUsername(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setUpdateTime(resultSet.getDate("update_time"));
                model.setEnable(resultSet.getInt("enable"));
                model.setHobby(resultSet.getString("hobby"));
                model.setBirthday(resultSet.getString("birthday"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
